package com.ollobot.authorization.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ollobot.authorization.entity.EmailVerificationCode;
import org.apache.ibatis.annotations.*;

import java.time.LocalDateTime;
import java.util.Optional;

@Mapper
public interface EmailVerificationCodeRepository extends BaseMapper<EmailVerificationCode> {
    
    @Select("SELECT * FROM email_verification_codes WHERE email = #{email} AND code = #{code} AND used = false")
    Optional<EmailVerificationCode> findByEmailAndCodeAndUsedFalse(@Param("email") String email, @Param("code") String code);
    
    @Select("SELECT * FROM email_verification_codes WHERE email = #{email} AND used = false AND expires_at > #{now} ORDER BY created_at DESC LIMIT 1")
    Optional<EmailVerificationCode> findLatestValidCodeByEmail(@Param("email") String email, @Param("now") LocalDateTime now);
    
    @Update("UPDATE email_verification_codes SET used = true WHERE email = #{email} AND used = false")
    void markAllCodesAsUsedByEmail(@Param("email") String email);
    
    @Delete("DELETE FROM email_verification_codes WHERE expires_at < #{now}")
    void deleteExpiredCodes(@Param("now") LocalDateTime now);
}
